package zry.Achievement.Thesis;

import zry.Search.SearchCriteriaThesis;
import zry.Util.DBUtil;
import Connection.*;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class ThesisDAOImpl extends DAOBase implements ThesisDAO {
    private static final String THESIS_INSERT_SQL = "INSERT INTO thesis_table(achievement_id,thesis_name,journals_name,thesis_type,thesis_time,index_type,library_type,supporting) VALUES(?,?,?,?,?,?,?,?)";
    private static final String THESIS_UPDATE_SQL = "UPDATE thesis_table SET thesis_name=?,journals_name=?,thesis_type=?,thesis_time=?,index_type=?,library_type=?,supporting=? WHERE achievement_id=?";
    private static final String THESIS_SELECT_SQL = "SELECT * FROM thesis_table WHERE achievement_id=?";
    private static final String THESIS_SELECT_ALL_SQL = "SELECT * FROM thesis_table";

    @Override
    public boolean addThesis(Thesis thesis) {
        Connection con = null;
        try {
            con = getConnection();
            PreparedStatement psmt = con.prepareStatement(THESIS_INSERT_SQL);
            psmt.setString(1, DBUtil.fixSqlFieldValue(thesis.getA_id()));
            psmt.setString(2, DBUtil.fixSqlFieldValue(thesis.getT_name()));
            psmt.setString(3, DBUtil.fixSqlFieldValue(thesis.getJ_name()));
            psmt.setString(4, DBUtil.fixSqlFieldValue(thesis.getT_type()));
            psmt.setDate(5, thesis.getT_time());
            psmt.setString(6, DBUtil.fixSqlFieldValue(thesis.getI_type()));
            psmt.setString(7, DBUtil.fixSqlFieldValue(thesis.getL_type()));
            psmt.setString(8, DBUtil.fixSqlFieldValue(thesis.getT_supporting()));
            psmt.executeUpdate();
            psmt.close();
        } catch (Exception e) {
            e.printStackTrace();
            return false;
        } finally {
            try {
                con.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        return true;
    }

    @Override
    public boolean updateThesis(Thesis thesis) {
        Connection con = null;
        try {
            con = getConnection();
            PreparedStatement psmt = con.prepareStatement(THESIS_UPDATE_SQL);
            psmt.setString(1, DBUtil.fixSqlFieldValue(thesis.getT_name()));
            psmt.setString(2, DBUtil.fixSqlFieldValue(thesis.getJ_name()));
            psmt.setString(3, DBUtil.fixSqlFieldValue(thesis.getT_type()));
            psmt.setDate(4, thesis.getT_time());
            psmt.setString(5, DBUtil.fixSqlFieldValue(thesis.getI_type()));
            psmt.setString(6, DBUtil.fixSqlFieldValue(thesis.getL_type()));
            psmt.setString(7, DBUtil.fixSqlFieldValue(thesis.getT_supporting()));
            psmt.setString(8, DBUtil.fixSqlFieldValue(thesis.getA_id()));

            psmt.executeUpdate();
            psmt.close();
        } catch (Exception e) {
            e.printStackTrace();
            return false;
        } finally {
            try {
                con.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        return true;
    }

    @Override
    public Thesis getThesis(String achievement_id) {
        Thesis thesis = null;
        Connection con = null;
        try {
            con = getConnection();
            PreparedStatement psmt = con.prepareStatement(THESIS_SELECT_SQL);
            psmt.setString(1, achievement_id);
            ResultSet rs = psmt.executeQuery();
            if (rs.next()) {
                thesis = new Thesis();
                thesis.setA_id(rs.getString("achievement_id"));
                thesis.setT_name(rs.getString("thesis_name"));
                thesis.setJ_name(rs.getString("journals_name"));
                thesis.setT_type(rs.getString("thesis_type"));
                thesis.setT_time(rs.getDate("thesis_time"));
                thesis.setI_type(rs.getString("index_type"));
                thesis.setL_type(rs.getString("library_type"));
                thesis.setT_supporting(rs.getString("supporting"));
            }
            rs.close();
            psmt.close();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                con.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        return thesis;
    }

    public List<Thesis> getAllThesises() {
        List<Thesis> thesises = new ArrayList<>();
        Connection con = null;
        try {
            con = getConnection();
            Statement stmt = con.createStatement();
            ResultSet rs = stmt.executeQuery(THESIS_SELECT_ALL_SQL);
            while (rs.next()) {
                Thesis thesis = new Thesis();
                thesis.setA_id(rs.getString("achievement_id"));
                thesis.setT_name(rs.getString("thesis_name"));
                thesis.setJ_name(rs.getString("journals_name"));
                thesis.setT_type(rs.getString("thesis_type"));
                thesis.setT_time(rs.getDate("thesis_time"));
                thesis.setI_type(rs.getString("index_type"));
                thesis.setL_type(rs.getString("library_type"));
                thesis.setT_supporting(rs.getString("supporting"));
                thesises.add(thesis);
            }
            rs.close();
            stmt.close();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                con.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        return thesises;
    }

    @Override
    public List<Thesis> findThesises(SearchCriteriaThesis searchCriteriaThesis) {
        List<Thesis> thesises = new ArrayList<>();
        Connection con = null;
        try {
            // 构建SQL语句
            StringBuilder sqlBuilder = new StringBuilder("SELECT * FROM thesis_table WHERE 1=1");
            if (searchCriteriaThesis.getA_id() != null) {
                sqlBuilder.append(" AND achievement_id LIKE ?");
            }
            if (searchCriteriaThesis.getT_name() != null) {
                sqlBuilder.append(" AND thesis_name LIKE ?");
            }
            if (searchCriteriaThesis.getJ_name() != null) {
                sqlBuilder.append(" AND journals_name LIKE ?");
            }
            if (searchCriteriaThesis.getT_type() != null) {
                sqlBuilder.append(" AND thesis_type LIKE ?");
            }
            if (searchCriteriaThesis.getT_time() != null) {
                sqlBuilder.append(" AND thesis_time LIKE ?");
            }
            if (searchCriteriaThesis.getI_type() != null) {
                sqlBuilder.append(" AND index_type LIKE ?");
            }
            if (searchCriteriaThesis.getL_type() != null) {
                sqlBuilder.append(" AND library_type LIKE ?");
            }
            if (searchCriteriaThesis.getT_supporting() != null) {
                sqlBuilder.append(" AND supporting LIKE ?");
            }
            String sql = sqlBuilder.toString();

            // 执行查询
            con = getConnection();
            PreparedStatement psmt = con.prepareStatement(sql);
            int index = 1;
            if (searchCriteriaThesis.getA_id() != null) {
                psmt.setString(index++, searchCriteriaThesis.getA_id());
            }
            if (searchCriteriaThesis.getT_name() != null) {
                psmt.setString(index++, searchCriteriaThesis.getT_name());
            }
            if (searchCriteriaThesis.getJ_name() != null) {
                psmt.setString(index++, searchCriteriaThesis.getJ_name());
            }
            if (searchCriteriaThesis.getT_type() != null) {
                psmt.setString(index++, searchCriteriaThesis.getT_type());
            }
            if (searchCriteriaThesis.getT_time() != null) {
                psmt.setDate(index++, searchCriteriaThesis.getT_time());
            }
            if (searchCriteriaThesis.getI_type() != null) {
                psmt.setString(index++, searchCriteriaThesis.getI_type());
            }
            if (searchCriteriaThesis.getL_type() != null) {
                psmt.setString(index++, searchCriteriaThesis.getL_type());
            }
            if (searchCriteriaThesis.getT_supporting() != null) {
                psmt.setString(index++, searchCriteriaThesis.getT_supporting());
            }
            ResultSet rs = psmt.executeQuery();
            while (rs.next()) {
                String a_id = rs.getString("achievement_id");
                String t_name = rs.getString("thesis_name");
                String j_name = rs.getString("journals_name");
                String t_type = rs.getString("thesis_type");
                Date t_time = rs.getDate("thesis_time");
                String i_type = rs.getString("index_type");
                String l_type = rs.getString("library_type");
                String t_supporting = rs.getString("supporting");
                Thesis thesis = new Thesis(a_id, t_name, j_name, t_type, t_time, i_type, l_type, t_supporting);
                thesises.add(thesis);
            }
            rs.close();
            psmt.close();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                con.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        return thesises;
    }
}
